/*
 * Copyright 2012 Victor.
 */
package lab1.lib.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import lab1.models.Employee;

/**
 * 
 * @todo Решить со значениями, которые возвращает save и delete. Либо true/false, либо кидать эксепшены
 * @author Victor
 */
public class EmployeeTable
{
    /**
     * Get employee.
     * 
     * @param id
     * @return employee
     */
    public static Employee getById(Integer id)
    {
        java.sql.Connection conn = null;
        try
        {
            Employee employee = new Employee();
            conn = DataBase.getConnection();
            PreparedStatement stat = conn.prepareStatement(
                    "SELECT * FROM emp e, dept d, salegrade s "
                    + "WHERE e.sal BETWEEN s.losal AND hisal "
                    + "AND e.deptno = d.deptno "
                    + "AND e.empno = ?");
            stat.setInt(1, id);
            ResultSet rs = stat.executeQuery();
            while(rs.next())
            {
                employee.setId(rs.getInt("empno"));
                employee.setName(rs.getString("ename"));
                employee.setJob(rs.getString("job"));
                employee.setManager(getById(rs.getInt("mgr")));
                employee.setHiredate(rs.getDate("hiredate"));
                employee.setSalary(rs.getInt("sal"));
                employee.setComm(rs.getInt("comm"));
                employee.setDepartment(DepartmentTable.getById(rs.getInt("deptno")));
                employee.setSalegrade(SaleGradeTable.getById(rs.getInt("grade")));
            }
            
            return employee;
        } catch (SQLException ex)
        {
            Logger.getLogger(EmployeeTable.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex)
        {
            Logger.getLogger(EmployeeTable.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try
            {
                conn.close();
            } catch (SQLException sQLException)
            {
            }
        }
        return null;
    }
    
    /**
     * Save employee.
     * 
     * @param employee
     * @return status
     */
    public static boolean save(Employee employee)
    {
        java.sql.Connection conn = null;
        try
        {
            conn = DataBase.getConnection();
            PreparedStatement stat;
            if (employee.getId() == null)
            {
                //add emp
                stat = conn.prepareStatement(
                        "INSERT INTO emp "
                        + "(ename, job, mgr, hiredate, sal, comm, deptno) "
                        + "VALUES "
                        + "(?, ?, ?, ?, ?, ?, ?)");
            } else
            {
                //update
                stat = conn.prepareStatement(
                        "UPDATE emp SET "
                        + "ename = ?, job = ?, mgr = ?, hiredate = ?,"
                        + "sal = ?, comm = ?, deptno = ? "
                        + "WHERE empno = ? ");
            }
            
            stat.setString(1, employee.getName());
            stat.setString(2, employee.getJob());
            if (employee.getManager() != null)
                stat.setInt(3, employee.getManager().getId());
            else
                stat.setNull(3, java.sql.Types.NULL);
            stat.setObject(4, employee.getHiredate());
            stat.setInt(5, employee.getSalary());
            if (employee.getComm() != null)
                stat.setInt(6, employee.getComm());
            else
                stat.setNull(6, java.sql.Types.NULL);
            stat.setInt(7, employee.getDepartment().getId());
            if (employee.getId() != null)
                stat.setInt(8, employee.getId());
            
            int count = stat.executeUpdate();

            if (count == 0)
                return false;
            else
                return true;
        } catch (SQLException ex)
        {
            Logger.getLogger(EmployeeTable.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex)
        {
            Logger.getLogger(EmployeeTable.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try
            {
                conn.close();
            } catch (SQLException sQLException)
            {
            }
        }
        return false;
    }
    
    /**
     * Delete employee.
     * 
     * @param employee
     * @return status
     */
    public static boolean delete(Employee employee)
    {
        java.sql.Connection conn = null;
        try
        {
            if (employee.getId() == null)
                return false;
            
            conn = DataBase.getConnection();
            PreparedStatement stat = conn.prepareStatement(
                    "DELETE FROM emp WHERE empno = ?");

            stat.setInt(1, employee.getId());
            int count = stat.executeUpdate();

            if (count == 0)
                return false;
            else
                return true;
        } catch (SQLException ex)
        {
            Logger.getLogger(EmployeeTable.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex)
        {
            Logger.getLogger(EmployeeTable.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try
            {
                conn.close();
            } catch (SQLException sQLException)
            {
            }
        }
        return false;
    }
}
